using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
 

namespace PHSRAG.SharePointUtility
{
    public class DocumentMigration
    {
        DocumentLibrary documentLibrary = new DocumentLibrary();


        public void UploadToSharepoint(int numberOfProtocols)
        {
            //Todo string builder
           
            
            SqlConnection connection = null;
            try
            {
               connection = new SqlConnection("data source=phssql176;initial catalog=insight_qa;integrated security=false;persist security info=True;User ID=insight;Password=insight");
               connection.Open();
                DataSet ds = GetDocumentList(numberOfProtocols, connection);
               
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    UploadAndHandleException(dr, connection);
                  
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.EventLog.WriteEntry("Insight", ex.Message);

            }
            finally
            {
                connection.Close();
            
            }
        }

        /// <summary>
        /// Upload file and in case of exception catch it and carry on
        /// </summary>
        /// <param name="dr"></param>
        /// <param name="connection"></param>
        public void UploadAndHandleException(DataRow dr, SqlConnection connection)
        {
            try
            {
                string url = string.Empty;
               
                byte[] blob = (byte[])dr["blob"];
                string fileName =  dr["Name"].ToString();
                string filExtension = fileName.Substring(fileName.LastIndexOf('.'), fileName.Length-(fileName.LastIndexOf('.')));

                if (DBNull.Value.Equals(dr["PROTOCOLNUMBER"]))
                {
                    url += DateTime.Now.Year + "/" + dr["PROTOCOLID"].ToString() + "/" + dr["DOCUMENTID"] + filExtension;
                }
                else
                {

                    url += dr["PROTOCOLNUMBER"].ToString().Substring(0, 4) + "/" + dr["PROTOCOLID"].ToString() + "/" + dr["DOCUMENTID"] + filExtension;
                }

                //documentLibrary.SaveFileToSharePoint(url, blob);


                MarkAsUploaded(Convert.ToInt32(dr["DOCUMENTID"]), connection);
            
            }
            catch (Exception ex)
            {

                System.Diagnostics.EventLog.WriteEntry("Insight", ex.Message + dr["DOCUMENTID"].ToString());
            }
        }

        public DataSet GetDocumentList(int numberOfProtocols, SqlConnection connection)
        {

            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "GetProtocolDocumentsForMigration";
            SqlParameter p1 = new SqlParameter("@NumberOfProtocols", DbType.Int32);
            p1.Value = numberOfProtocols;
            command.Parameters.Add(p1);
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds);

            return ds;
        }

        public void MarkAsUploaded(int docid, SqlConnection connection)
        {

            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UpdateStatusofUploadedDocument";
            SqlParameter docId = new SqlParameter("@docid", DbType.Int32);
            docId.Value = docid;
            command.Parameters.Add(docId);
            command.ExecuteNonQuery();
            
        }



    }
}
